<%@ Import Namespace="GUSNET" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Data.OleDb" %>
<%@ Import Namespace="System.Data" %>
<%@ Register TagPrefix="GUS" TagName="BibleBookList" src="BibleBookList.ascx" %>
<%@ Register TagPrefix="GUS" TagName="AdminFooter" src="AdminFooter.ascx" %>
<%@ Register TagPrefix="GUS" TagName="AdminHeader" src="AdminHeader.ascx" %>
<%@ Register TagPrefix="GUS" TagName="SysProfile" src="./include\SysProfile.ascx" %>
<!DOCTYPE html public "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
	<HEAD>
		<TITLE>Bible Reading - Spring of Water</TITLE>
		<script language="VB" runat="server">


   Sub Page_Load( sender as Object,  e as EventArgs ) 
   	Dim AndOr as string
   	 if not (Page.IsPostBack) then
	 	TitleID.Text = Request.QueryString("TitleID")
	 	ChapterID.Text = Request.QueryString("ChapterID")
		if Trim(Request.QueryString("Keyword")) <>"" then
		 	Keyword.Text = Request.QueryString("Keyword")
			AndOr = Trim(Request.QueryString("AndOr"))
			if AndOr = "OR" then
				RadioOR.Checked = true
			else
				RadioAND.Checked = true
			end if
		end if
		''ReturnURL.NavigateUrl = Request.UrlReferrer.ToString()

		if ChapterID.Text = ""	 then 
			ChapterID.Text = "1"
		end if 
		
		if Keyword.Text <>"" then
			ChapterList.Text = "Search: " + Keyword.Text
			BookChapStr.Text = ""
			VerseList.Text   = GetFoundedVerseListStr(Keyword.Text)
		elseif TitleID.Text <>""  then
			ChapterList.Text = GetChapterListStr(TitleID.Text, ChapterID.Text)
			BookChapStr.Text = GetThisBookChapterStr()
			VerseList.Text   = GetVerseListStr(TitleID.Text, ChapterID.Text)
		else
			VerseList.Text  = "<div align=center><img Src='images/word.jpg'></div>"
		end if
		
		
	 end if 
	 ''ShowImgs()
	 ''testbible()
   End Sub
   
   
   sub testbible()
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 sql = "SELECT         Title_ID, Title_CN, Title_CNAB, Title_EN, Title_ENAB, Title_New FROM BibleTitle"
	 dbr = obj.GetSQLDataReader(sql)
	  while (dbr.Read())
	  	Response.Write(dbr.GetValue(1).ToString() + "<br>")
	  End While 
	  dbr.Close()
   end sub

   Function GetThisBookChapterStr()
   	 Dim sql, st, ThisTitle as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 
	 If TitleID.Text = "" then
	 	Exit Function
	 end if
	 sql = "SELECT  Title_ID, Title_CN, Title_CNAB, Title_EN, Title_ENAB, Title_New FROM BibleTitle where Title_ID='"+ TitleID.Text +"'"
	 'Response.Write(sql)
	 'exit function
	 dbr = obj.GetSQLDataReader(sql)
	  while (dbr.Read())
	  	ThisTitle = dbr.GetValue(1).ToString() 
	  End While 
	  dbr.Close()
	  
	  st = "<b>" + ThisTitle + "</b>"
	  if ChapterID.Text <>"" and st <>"" then
	  	st = st + "<i> [Chap: <b>"+ ChapterID.Text +"</b> ]</i> <br>"
	  end if 
	  
	  GetThisBookChapterStr = st 
   end Function
   
   Function GetChapterListStr(tID, cID)
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 Dim st as string
	 DIM maxChap , i
	 maxChap = 0
	 sql = "SELECT MAX(BibleContent.Content_Chap) AS Expr1 FROM BibleTitle INNER JOIN BibleContent ON BibleTitle.Title_ID = BibleContent.Content_Title WHERE         (BibleTitle.Title_ID = "+ tID +")"
	 dbr = obj.GetSQLDataReader(sql)
	  while (dbr.Read())
	  	maxChap = dbr.GetValue(0)
	  End While 
	  dbr.Close()
	  
	  for i = 1 to maxChap
	  	if cID.ToString = i.ToString() then
		  	st = st + "<b>["+ i.ToString() +"]</b> "
		else
		  	st = st + "<a href='/springofwater/bibletool/ReadBible.aspx?TitleID="+ tID.ToString() +"&ChapterID="+ i.ToString() +"'><font color='#ffff66'>["+ i.ToString() +"]</font></a> "
		end if 
	  next
	  GetChapterListStr = st
   End Function 
   
   Function GetVerseListStr(tID, cID)
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 Dim st as string
	 DIM maxChap , i
	 maxChap = 0
	 sql = "SELECT  BibleContent.Content_Chap, BibleContent.Content_Sec, BibleContent.Content_CN FROM  BibleTitle INNER JOIN BibleContent ON " & _
	 "  BibleTitle.Title_ID = BibleContent.Content_Title WHERE (BibleTitle.Title_ID = '"& tID &"') and (BibleContent.Content_Chap='"& cID &"')"& _
	 " ORDER BY  BibleContent.Content_Chap, BibleContent.Content_Sec "
	 dbr = obj.GetSQLDataReader(sql)
	 st = "<table>"
	 i = 0
	  while (dbr.Read())
	  	if (i mod 2 = 0) then
		  	st = st & "<tr valign=top  bgcolor='#FFFFFF'>" & vbcrlf
		else
		  	st = st & "<tr valign=top  bgcolor='#e3edf2'>" & vbcrlf
		end if
		st = st & "<TD width='5%' align=center><FONT color='#0033ff'>"&  dbr.GetValue(1)  &"</FONT></TD>" & vbcrlf
		st = st & "<td width=95% align=left><P class='size1'><FONT color='#0033ff'>"&  dbr.GetValue(2)  &"</FONT></td>" & vbcrlf
		st = st + "</tr>" & vbcrlf
		st = st + "<TR><TD></TD></TR>" & vbcrlf
		i = i  + 1
	  End While 
	  st = st & "</table>"
	  dbr.Close()
	  
	  GetVerseListStr = st
   End Function 
   
   
   Function GetFoundedVerseListStr(keywd)
   	 Dim sql as String
	 Dim  ConnStr as String = SysProfile.ConnectionString
	 Dim obj as GUSDBUtil =   new GUSDBUtil (ConnStr)
	 Dim  dbr as SqlDataReader
	 Dim st as string
	 DIM maxChap , i
	 DIM encodeKeywd
	 Dim encode   as System.Text.Encoding 
	 Dim AndOr as string
	 Dim Verse as String
	 
	 if RadioOR.Checked then 
	 	AndOr = "OR" 
	 else
	 	AndOr = "AND"
	 End if 
	 
	'encode = System.Text.Encoding.GetEncoding("utf-8")

	''encodeKeywd = encode.GetString(keywd, 0, len(keywd))
	
	encodeKeywd = keywd
	
	Dim keywdAry() as string
	keywdAry = keywd.split(" ")

	
	 maxChap = 0
	 sql = ""

	 sql = "SELECT BibleTitle.Title_ID, Title_CN, Title_CNAB, BibleContent.Content_Chap, BibleContent.Content_Sec, BibleContent.Content_CN FROM  BibleTitle INNER JOIN BibleContent ON " & _
	 "  BibleTitle.Title_ID = BibleContent.Content_Title WHERE "
	 
	 if AndOr = "OR" then
	 	sql = sql & " (0=1)"
	 else
	 	''and 
	 	sql = sql & " (1=1)"
	 end if
	 
	for i = 0 to keywdAry.length-1
		if keywdAry(i).Trim() <>"" then
			sql = sql &  AndOr  & " (BibleContent.Content_CN like '%"& keywdAry(i).Trim() &"%')"
		end if
	next
	 sql = sql & " ORDER BY  BibleTitle.Title_ID, BibleContent.Content_Chap, BibleContent.Content_Sec "


	 ''Response.Write(sql)
	 'Response.end()
	 dbr = obj.GetSQLDataReader(sql)
	 st = "<table>"
	  while (dbr.Read())
	  	if (maxChap mod 2 = 0) then
		  	st = st & "<tr valign=top  bgcolor='#FFFFFF'>" & vbcrlf
		else
		  	st = st & "<tr valign=top  bgcolor='#e3edf2'>" & vbcrlf
		end if
		st = st & "<TD width='5%' align=left nowrap><FONT color='#0033ff'><b>"&  dbr.GetValue(2)  & dbr.GetValue(3) & ":" & dbr.GetValue(4)   &"</b></FONT></TD>" & vbcrlf
		st = st & "<td width=95% align=left><P class='size1'><FONT color='#0033ff'>"
		Verse = dbr.GetValue(5).ToString().Trim()
		for i = 0 to keywdAry.length-1
			if keywdAry(i).Trim() <>"" then
			 Verse = Verse.Replace(keywdAry(i).ToString().Trim(), "<font color=red>" + keywdAry(i).Trim() + "</font>")   
			end if 
		next
		st = st & Verse
		st = st &	 "</FONT></td>" & vbcrlf
		st = st + "</tr>" & vbcrlf
		st = st + "<TR><TD></TD></TR>" & vbcrlf
		maxChap = maxChap +1
	  End While 
	  st = st & "</table>"
	  if maxChap >0 then
	  	st += "<hr><font face='Arial,Helvetica,sans-serif' size='2' color='Navy'><b>Total found: "& maxChap &"</font></b>"
	  else
	  	st += "Not Found !"
	  end if
	  dbr.Close()
	  
	  GetFoundedVerseListStr = st
   End Function 
   
   Sub SearchKeyword(sender As Object, e As EventArgs)
	  if Keyword.Text = "" then
	  	exit sub
	  end if  

			ChapterList.Text = "<img src=./images/view.gif><b> " + Keyword.Text + "</b>"
			BookChapStr.Text = ""
			VerseList.Text   = GetFoundedVerseListStr(Keyword.Text)

   End Sub
   
   
		</script>
		<LINK href="images/springofwater.css" type="text"='DESIGNTIMEURL="images/springofwater.css"'">
			<META content="False" name="vs_snapToGrid">
			<META content="False" name="vs_showGrid">
			<LINK href="sow.css" type="text"='DESIGNTIMEURL="sow.css"'">
				<META http-equiv="Content-Type" content="text/html; charset=gb2312">
				<SCRIPT language="JavaScript">// -->
				</SCRIPT>
				<META content="MSHTML 6.00.3790.2491" name="GENERATOR">
	</HEAD>
	<BODY>
		<GUS:SysProfile id="SysProfile" runat="server" />
		<Form runat="server">
			<!--#include file="Top.html"-->
			<DIV align="center">
				<TABLE id="table81" style="WIDTH: 740px; HEIGHT: 589px" height="589" width="740" border="0">
					<TBODY>
						<TR>
							<TD valign="top" align="left" width="169">
								<P>
									<TABLE id="Table1" style="WIDTH: 197px; HEIGHT: 535px" height="535" cellspacing="1" cellpadding="1"
										width="197" border="0">
										<TBODY>
											<TR>
												<TD bordercolor="blue" height="19">&nbsp;
												</TD>
											</TR>
											<TR>
												<TD valign="top" align="left">
													<div>
														<TABLE id="table63" style="BORDER-COLLAPSE: collapse" bordercolor="#5a89b4" width="192"
															border="1">
															<TBODY>
																<TR>
																	<TD valign="middle" align="left" bgcolor="#5d9dd1" height="27"><FONT color="#000000" face="Arial,Helvetica,sans-serif"><b>Bible 
																				Search:</b></FONT>
																	</TD>
																</TR>
																<TR>
																	<TD height="64"><P class="MsoNormal">
																			<font face="Arial,Helvetica,sans-serif" size="2">Keyword:
																				<asp:TextBox Id="Keyword" RunAt="server" size="15" /><br>
																				Logical:
																				<asp:RadioButton id="RadioAND" Checked="True" GroupName="AndOrGroup" Text="AND" runat="server" />
																				<asp:RadioButton id="RadioOR" Checked="False" GroupName="AndOrGroup" Text="OR" runat="server" /><br>
																			</font>
																		</P>
																	</TD>
																</TR>
																<TR>
																	<TD align="right">
																		<Asp:button id="Search" OnClick="SearchKeyword" Text="Search" runat="server" />
																		<!-- <P class="MsoNormal"><FONT size="2"><A href="mailto:contact@springofwater.net"><FONT color="#0000ff">Contact Us</FONT></A></FONT></P> -->
																	</TD>
																</TR>
															</TBODY>
														</TABLE>
													</div>
													<TABLE id="table63" style="BORDER-COLLAPSE: collapse" bordercolor="#5a89b4" height="318"
														width="192" border="1">
														<TBODY>
															<TR>
																<TD valign="middle" align="left" bgcolor="#5d9dd1" height="27"><FONT color="#000000" face="Arial,Helvetica,sans-serif"><b>Bible 
																			Reading:</b></FONT>
																</TD>
															</TR>
															<TR>
																<TD height="64"><P class="MsoNormal">
																		<SPAN lang="ZH-CN" style="FONT-FAMILY: Times New Roman">
																			<FONT size="2">
																				<GUS:BibleBookList id="BibleBookList" runat="server" />
																			</FONT>
																		</SPAN></P>
																</TD>
															</TR>
															<TR>
																<TD align="right">
																	<P class="MsoNormal"><FONT size="2"><A href="mailto:contact@springofwater.net"><FONT color="#0000ff">Contact 
																					Us</FONT></A></FONT></P>
																</TD>
															</TR>
														</TBODY>
													</TABLE>
												</TD>
											</TR>
										</TBODY>
									</TABLE>
								</P>
							</TD>
							<TD valign="top" align="left" width="5680">
								<div align="center"><img src="./images/study.gif"> <img src="./images/title-bibletool.gif"></div>
								<TABLE id="Table5" style="WIDTH: 568px; BORDER-COLLAPSE: collapse; HEIGHT: auto" bordercolor="#5a89b4"
									cellpadding="2" width="297" border="1">
									<TBODY>
										<TR>
											<TD valign="top" align="left" bgcolor="#5d9dd1" height="14"><FONT color="#000000">
													<asp:Label Id="BookChapStr" RunAt="server" Text="" Visible="true" />
													<asp:Label Id="ChapterList" RunAt="server" Text="" Visible="true" />
												</FONT>
											</TD>
										</TR>
										<TR>
											<TD valign="bottom" align="left" height="22">
												<!--
			<LI>BookID:  <asp:Label Id="TitleID" RunAt="server" Text=""  Visible="false"/>
			<LI>Chapter: <asp:Label Id="ChapterID" RunAt="server" Text=""  Visible="false" /> <br>
			-->
												<asp:Label Id="VerseList" RunAt="server" Text="" Visible="true" />
											</TD>
										</TR>
									</TBODY>
								</TABLE>
							</TD>
						</TR>
					</TBODY>
				</TABLE>
				</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
			</DIV> <!--#include file="copyright.html"-->
		</Form>
	</BODY>
</HTML>
